<?php
// +----------------------------------------------------------------------
// | zlserver [ 小程序 ]
// +----------------------------------------------------------------------
// | Copyright (c) 2017~2018 http://www.zhongliansoft.com All rights reserved.
// +----------------------------------------------------------------------
// | Author: tony <tanyu@zhongliansoft.com>
// +----------------------------------------------------------------------

namespace app\job\export;

use think\queue\Job;
use app\common\model\Ietask;
use app\common\model\WxDsBill as WxDsBillModel;
use think\facade\Log;

class WxReconciliationFds
{
    //执行导出任务
    public function exec(Job $job, $params)
    {
        $ietaskModle = new Ietask();
        
        // 查询导出内容
        $filter = json_decode($params['params'], true);
 
        $date_re = $this->getDate(urldecode($filter['date']), $filter['section']);
        if(!$date_re['status']){
            return $date_re;
        }
        $status = urldecode($filter['status']);
  
        $xData = $this->getXdata($date_re['data']);
        if(!$xData['status']){
            return $xData;
        }
 
        $sysStatus = 1;
        $sysList = $this->getSysFdsBill($date_re, $sysStatus);
        $wxList = $this->getWxFdsBill($date_re, $sysStatus);
        $refundWxList = $this->getRefundWxFdsBill($date_re, $sysStatus);
        
        //组装数据列表用于table里使用
        $table = [];
        $sys_num = 0;
        $sys_val = 0;
        $wx_num = 0;
        $wx_val = 0;
        $refund_wx_num = 0;
        $refund_wx_val = 0;

        for($i = 0;$i< $date_re['data']['num'];$i++){
            $item = [];
            $item['x'] = $xData['data'][$i];
            $item['sys_num'] = $sysList[$i]['sys_num'];
            $item['sys_val'] = $sysList[$i]['sys_val'];
            $item['wx_num'] = $wxList[$i]['wx_num'];
            $item['wx_val'] = $wxList[$i]['wx_val'];
            $item['refund_wx_num'] = $refundWxList[$i]['wx_num'];
            $item['refund_wx_val'] = $refundWxList[$i]['wx_val'];
            $item['status'] = '失败';
            if(($item['sys_num'] == ($item['wx_num'] - $item['refund_wx_num'])) && ($item['sys_val'] == ($item['wx_val'] - $item['refund_wx_val']))){
                $item['status'] = '成功';
            }

            if($status == $item['status'] || $status == ''){
                $sys_num += $item['sys_num'];
                $sys_val += $item['sys_val'];
                $wx_num += $item['wx_num'];
                $wx_val += $item['wx_val'];
                $refund_wx_num += $item['refund_wx_num'];
                $refund_wx_val += $item['refund_wx_val'];

                $table[] = $item;
            }
        }

        $itemTotal = [];
        $itemTotal['x'] = '合计';
        $itemTotal['sys_num'] = $sys_num;
        $itemTotal['sys_val'] = $sys_val;
        $itemTotal['wx_num'] = $wx_num;
        $itemTotal['wx_val'] = $wx_val;
        $itemTotal['refund_wx_num'] = $refund_wx_num;
        $itemTotal['refund_wx_val'] = $refund_wx_val;

        if(($sys_num == ($wx_num - $refund_wx_num)) && ($sys_val == ($wx_val - $refund_wx_val))){
            $itemTotal['status'] = '成功';
        } else {
            $itemTotal['status'] = '失败';
        }

        $table[] = $itemTotal;

        $template = config('zlserver.wxreconciliation_export_templete');     //使用模板
        $objPHPExcel = \PHPExcel_IOFactory::load($template);     //加载excel文件,设置模板 
        $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);  //设置保存版本格式  
        
        //接下来就是写数据到表格里面去  
        $objActSheet = $objPHPExcel->getActiveSheet();
        $i = 2;  
        foreach ($table as $key => $val) {
            //这里是设置单元格的内容  
            $objActSheet->setCellValue('A'.$i, $val['x']); 
            $objActSheet->setCellValue('B'.$i, $val['sys_num']); 
            $objActSheet->setCellValue('C'.$i, $val['sys_val']); 
            $objActSheet->setCellValue('D'.$i, $val['wx_num']);
            $objActSheet->setCellValue('E'.$i, $val['wx_val']);
            $objActSheet->setCellValue('F'.$i, $val['refund_wx_num']); 
            $objActSheet->setCellValue('G'.$i, $val['refund_wx_val']); 
            $objActSheet->setCellValue('H'.$i, $val['status']);

            $i++;
        }
        
        $file_name = "wxreconciliation-xls-" . date("YmdHis", time()) . ".xls";
        $filePath  = defined("FILE_PATH") ? FILE_PATH : ROOT_PATH . 'public' . DS . 'static' . DS . 'file' . get_hash_dir($file_name);
        if (!is_dir($filePath)) {
            @mkdirs($filePath, 0777, true);
        } 
        $fullFilePath = $filePath . $file_name;
        // 1.保存至本地Excel表格  
        $objWriter->save($fullFilePath);
            
        $uData['file_name'] = $file_name;
        $uData['count'] = count($table);
        $uData['message'] = '导出成功';
        $uData['file_size'] = getRealSize(filesize($fullFilePath));
        $uData['file_path'] = $fullFilePath;
        $uData['status'] = $ietaskModle::EXPORT_SUCCESS_STATUS;
        $uData['utime'] = time();
        $ietaskModle->update($uData, ['id' => $params['task_id']]);
        
        $job->delete();
       
        if ($job->attempts() > 3) {
            $uData['status'] = $ietaskModle::EXPORT_FAIL_STATUS;
            $uData['message'] = '导出执行失败';
            $uData['utime'] = time();
            $ietaskModle->update($uData, ['id' => $params['task_id']]);
            $job->delete();
        }
        /*$job->release($delay); //$delay为延迟时间*/
    }

    public function failed($data)
    {

        // ...任务达到最大重试次数后，失败了
    }

    
    //根据时间，返回时间段
    private function getDate($date, $section){
        $result = [
            'status' => false,
            'data'   => [],
            'msg'    => '',
        ];
        if($date){
            $theDate = explode(' 到 ', $date);
            if(count($theDate) == 2){
                $data['start'] = strtotime($theDate[0]);
                $data['end'] = strtotime($theDate[1])+60*60*24;
                $data['section'] = 1;
            }else{
                $result['msg'] = '时间段格式不正确';
                return $result;
            }
        }else{
            return error_code(10000);
        }

        //切片维度，1是小时，2是天，3是周，4是月，5是季度，6是半年，7是年
        if($section){
            $data['section'] = $section;
        }
        //算统计需要的参数
        return $this->getTmp($data);

    }

    //根据时间节点和时间粒度算x轴个数
    private function getTmp($date_arr){
        $result = [
            'status' => false,
            'data'   => $date_arr,
            'msg'    => '',
        ];
        if($date_arr['end'] <= $date_arr['start']){
            $result['msg']='开始时间必须小于等于结束时间';
            return $result;
        }

        $diff_day = $date_arr['end'] - $date_arr['start'];
        switch($date_arr['section']){
            case 1:                 //小时
                $result['data']['section'] = 60*60;
                break;
            case 2:                 //天
                $result['data']['section'] = 60*60*24;
                break;
            default:
                $result['msg'] = '没有此时间粒度';
                return $result;
        }
        //算x轴数据个数
        $result['data']['num'] = ceil($diff_day / $result['data']['section']);

        $result['status'] = true;
        return $result;

    }

    
    private function getXdata($date_arr){
        $result = [
            'status' => true,
            'data'   => [],
            'msg'    => '',
        ];

        //校验,x轴最多1000个
        $x_nums = ceil(($date_arr['end'] - $date_arr['start']) / $date_arr['section']);
        if($x_nums > 1000){
            return error_code(13226);
        }

        $x_type = "";
        switch($date_arr['section']){
            case 3600:                 //小时
                if($x_nums<= 24) {
                    $x_type = "H时";
                }elseif($x_nums <= 720){
                    $x_type = "d日H时";
                }else{
                    $x_type = "m月d日H时";
                }
                break;
            case 86400:                 //天
                $x_type = "Y-m-d";
                // if($x_nums <= 31){
                //     $x_type = "d号";
                // }elseif($x_nums <= 365){
                //     $x_type = "m.d";
                // }else{
                //     $x_type = "Y.m.d";
                // }
                break;
        }
        if($x_type == ""){
            return error_code(10000);
        }

        for($i = $date_arr['start'];$i<($date_arr['end']);$i=$i+$date_arr['section']){
            if($date_arr['section'] == 3600){
                $result['data'][] = date($x_type,$i+$date_arr['section']);      //小时是结尾的表示当前切片的时间
            }else{
                $result['data'][] = date($x_type,$i);       //天是开始表示当前切片的时间
            }

        }
        return $result;
    }
 
    
    // 账单对账
    private function getSysDsBill($date_re, $status)
    {
        $sql = "select tmp_x.x, ifnull(sum(b.jfje_),0) as sys_val,count(b.id_) as sys_num from
            (SELECT @xi:=@xi+1 as x from
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x1,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x2,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x3,
                (SELECT @xi:=-1) x0 limit 0,".$date_re['data']['num'].") tmp_x
            left join (
                select DISTINCT a.id_, a.jfje_, a.time_jf_ from zlzhsf.t_pay_order a, zlzhsf.t_pays_order_detail b, zlzhsf.psfxm c 
                where a.id_ = b.t_pays_order_id_ and b.sfxmdm_ = c.sfxmdm and c.sfxmlxdm in ('09')
                and is_qrzf_ = 1
                and UNIX_TIMESTAMP(time_jf_) > " .$date_re['data']['start']. "
                and UNIX_TIMESTAMP(time_jf_) < " .$date_re['data']['end'].  "
            ) b on tmp_x.x = (( cast(UNIX_TIMESTAMP(b.time_jf_) as signed) - ".$date_re['data']['start'].") div (".$date_re['data']['section']."))
            group by tmp_x.x
        ";

        $model = new WxDsBillModel();
        return $model->query($sql);
    }

    // 账单对账
    private function getWxDsBill($date_re, $status){
        $sql = "select tmp_x.x, ifnull(sum(b.total_amount),0) as wx_val,count(b.order_sn_wx) as wx_num from
            (SELECT @xi:=@xi+1 as x from
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x1,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x2,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x3,
                (SELECT @xi:=-1) x0 limit 0,".$date_re['data']['num'].") tmp_x
            left join (
                select a.order_sn_wx, total_amount, pay_time from `".config('database.prefix')."wx_ds_bill` a 
                where pay_status = 'SUCCESS'
                and UNIX_TIMESTAMP(pay_time) > " .$date_re['data']['start'].  "
                and UNIX_TIMESTAMP(pay_time) < " .$date_re['data']['end'].  "
            ) b on tmp_x.x = (( cast(UNIX_TIMESTAMP(b.pay_time) as signed) - ".$date_re['data']['start'].") div (".$date_re['data']['section'].")) 
            group by tmp_x.x
        ";

        $model = new WxDsBillModel();
        return $model->query($sql);
    }

    // 账单对账
    private function getRefundWxDsBill($date_re, $status){
        $sql = "select tmp_x.x, ifnull(sum(b.total_amount),0) as wx_val,count(b.order_sn_wx) as wx_num from
            (SELECT @xi:=@xi+1 as x from
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x1,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x2,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x3,
                (SELECT @xi:=-1) x0 limit 0,".$date_re['data']['num'].") tmp_x
            left join (
                select a.order_sn_wx, total_amount, pay_time from `".config('database.prefix')."wx_ds_bill` a join
                (SELECT order_sn_wx FROM `".config('database.prefix')."wx_ds_bill` WHERE pay_status = 'REFUND') b on a.order_sn_wx = b.order_sn_wx  
                where pay_status = 'SUCCESS'
                and UNIX_TIMESTAMP(pay_time) > " .$date_re['data']['start'].  "
                and UNIX_TIMESTAMP(pay_time) < " .$date_re['data']['end'].  "
            ) b on tmp_x.x = (( cast(UNIX_TIMESTAMP(b.pay_time) as signed) - ".$date_re['data']['start'].") div (".$date_re['data']['section'].")) 
            group by tmp_x.x
        ";

        $model = new WxDsBillModel();
        return $model->query($sql);
    }

    // 账单对账
    private function getSysFdsBill($date_re, $status)
    {
        $sql = "select tmp_x.x, ifnull(sum(b.jfje_),0) as sys_val,count(b.id_) as sys_num from
            (SELECT @xi:=@xi+1 as x from
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x1,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x2,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x3,
                (SELECT @xi:=-1) x0 limit 0,".$date_re['data']['num'].") tmp_x
            left join (
                select DISTINCT a.id_, a.jfje_, a.time_jf_ from zlzhsf.t_pay_order a, zlzhsf.t_pays_order_detail b, zlzhsf.psfxm c 
                where a.id_ = b.t_pays_order_id_ and b.sfxmdm_ = c.sfxmdm and c.sfxmlxdm in ('01', '02', '03')
                and is_qrzf_ = 1
                and UNIX_TIMESTAMP(time_jf_) > " .$date_re['data']['start'].  "
                and UNIX_TIMESTAMP(time_jf_) < " .$date_re['data']['end'].  "
            ) b on tmp_x.x = (( cast(UNIX_TIMESTAMP(b.time_jf_) as signed) - ".$date_re['data']['start'].") div (".$date_re['data']['section']."))
            group by tmp_x.x
        ";

        $model = new WxDsBillModel();
        return $model->query($sql);
    }

    // 账单对账
    private function getWxFdsBill($date_re, $status){
        $sql = "select tmp_x.x, ifnull(sum(b.total_amount),0) as wx_val,count(b.order_sn_wx) as wx_num from
            (SELECT @xi:=@xi+1 as x from
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x1,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x2,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x3,
                (SELECT @xi:=-1) x0 limit 0,".$date_re['data']['num'].") tmp_x
            left join (
                select a.order_sn_wx, total_amount, pay_time from `".config('database.prefix')."wx_fds_bill` a  
                where pay_status = 'SUCCESS'
                and UNIX_TIMESTAMP(pay_time) > " .$date_re['data']['start'].  "
                and UNIX_TIMESTAMP(pay_time) < " .$date_re['data']['end'].  "
            ) b on tmp_x.x = (( cast(UNIX_TIMESTAMP(b.pay_time) as signed) - ".$date_re['data']['start'].") div (".$date_re['data']['section'].")) 
            group by tmp_x.x
        ";

        $model = new WxDsBillModel();
        return $model->query($sql);
    }

    // 账单对账
    private function getRefundWxFdsBill($date_re, $status){
        $sql = "select tmp_x.x, ifnull(sum(b.total_amount),0) as wx_val,count(b.order_sn_wx) as wx_num from
            (SELECT @xi:=@xi+1 as x from
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x1,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x2,
                (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) x3,
                (SELECT @xi:=-1) x0 limit 0,".$date_re['data']['num'].") tmp_x
            left join (
                select a.order_sn_wx, total_amount, pay_time from `".config('database.prefix')."wx_fds_bill` a join
                (SELECT order_sn_wx FROM `".config('database.prefix')."wx_fds_bill` WHERE pay_status = 'REFUND') b on a.order_sn_wx = b.order_sn_wx  
                where pay_status = 'SUCCESS'
                and UNIX_TIMESTAMP(pay_time) > " .$date_re['data']['start'].  "
                and UNIX_TIMESTAMP(pay_time) < " .$date_re['data']['end'].  "
            ) b on tmp_x.x = (( cast(UNIX_TIMESTAMP(b.pay_time) as signed) - ".$date_re['data']['start'].") div (".$date_re['data']['section'].")) 
            group by tmp_x.x
        ";

        $model = new WxDsBillModel();
        return $model->query($sql);
    }
    
}